USE [NguyenHoa]
GO
-------------------------------------------------------------------------
--						@tblMultimedia
-------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblMultimedia_DeleteByIdList]') and type IN (N'PC', N'P'))
DROP PROCEDURE [dbo].[_tblMultimedia_DeleteByIdList]
GO

CREATE PROCEDURE [dbo].[_tblMultimedia_DeleteByIdList]
(
	@IdList nvarchar(256)
)
AS
	IF(@IdList IS NULL OR @IdList = '')
		SET @IdList = ''''''

	DECLARE @SQL NVARCHAR(2048)
	SET @SQL = 'DELETE FROM tblMultimedia WHERE [Id] IN (' + @IdList + ')'
	EXEC(@SQL)
GO
-------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblMultimedia_GetPhotoForHome]') and type IN (N'PC', N'P'))
DROP PROCEDURE [dbo].[_tblMultimedia_GetPhotoForHome]
GO

CREATE PROCEDURE [dbo].[_tblMultimedia_GetPhotoForHome]
AS
	SELECT *
	FROM tblMultimedia M JOIN tblAlbum A ON M.[AlbumId] = A.[Id]
	WHERE M.[Type] = 'P' AND M.[Status] = 'A' AND
				A.[Type] = 'G' AND A.[ShowOnHome] = 1
	SELECT @@RowCount
GO
-------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblMultimedia_GetAllVideo]') and type IN (N'PC', N'P'))
DROP PROCEDURE [dbo].[_tblMultimedia_GetAllVideo]
GO

CREATE PROCEDURE [dbo].[_tblMultimedia_GetAllVideo]
AS
	SELECT 
		M.*
	FROM	tblMultimedia M
	WHERE	M.[Type]='V'
GO
-------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblMultimedia_GetVideoByStatus]') and type IN (N'PC', N'P'))
DROP PROCEDURE [dbo].[_tblMultimedia_GetVideoByStatus]
GO

CREATE PROCEDURE [dbo].[_tblMultimedia_GetVideoByStatus]
(
	@Status char(1)
)
AS
	SELECT 
		M.*
	FROM	tblMultimedia M
	WHERE	M.[Status]=@Status AND
			M.[Type]='V'
GO
-------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblMultimedia_SaveStatusByIdList]') and type IN (N'PC', N'P'))
DROP PROCEDURE [dbo].[_tblMultimedia_SaveStatusByIdList]
GO

CREATE PROCEDURE [dbo].[_tblMultimedia_SaveStatusByIdList]
(
	@IdList nvarchar(256),
	@Status char(1)
)
AS
	IF(@IdList IS NULL OR @IdList = '')
		SET @IdList = ''''''
	IF(@Status IS NULL OR @Status='')
		SET @Status='A'
	DECLARE @SQL NVARCHAR(2048)
	SET @SQL = N'UPDATE tblMultimedia SET [Status]='''+@Status+''' WHERE [Id] IN (' + @IdList + ')'
	EXEC sp_executesql @SQL
GO

-------------------------------------------------------------------------
--						@tblAlbum
-------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblAlbum_GetByType]') and type IN (N'PC', N'P'))
DROP PROCEDURE [dbo].[_tblAlbum_GetByType]
GO

CREATE PROCEDURE [dbo].[_tblAlbum_GetByType]
(
	@Type CHAR(1),
	@Status CHAR(1),
	@CurrentLanguage INT
)
AS
	SELECT	A.*,
					ISNULL(L.[Name],A.[Name]) as AlbumName,
					[FirstPhoto] = '', [PhotoList] = ''
	FROM	tblAlbum A 
				INNER JOIN tblAlbumLocale L ON A.Id = L.AlbumId AND L.LanguageId = @CurrentLanguage
	WHERE (@Type IS NULL OR @Type = '' OR @Type = A.[Type]) AND
				(@Status IS NULL OR @Status = A.[Status])
	ORDER BY A.[Type], A.[Name]

	SELECT *
	FROM tblMultimedia M JOIN tblAlbum A ON M.[AlbumId] = A.[Id]
	WHERE M.[Type] = 'P' AND M.[Status] = 'A'
GO
-------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblAlbum_SetShowOnHome]') and type IN (N'PC', N'P'))
DROP PROCEDURE [dbo].[_tblAlbum_SetShowOnHome]
GO

CREATE PROCEDURE [dbo].[_tblAlbum_SetShowOnHome]
(
	@AlbumId INT,
	@Show BIT
)
AS
	BEGIN TRAN
		IF(@Show = 1)
			BEGIN
				UPDATE tblAlbum
				SET [ShowOnHome] = 0
				WHERE [Id] <> @AlbumId
		
				IF(@@Error <> 0)
					GOTO HANDLEERR
			END
		UPDATE tblAlbum
		SET [ShowOnHome] = @Show
		WHERE [Id] = @AlbumId
	
		IF(@@ERROR <> 0)
			GOTO HANDLEERR

	COMMIT TRAN
	RETURN
	HANDLEERR:
		ROLLBACK TRAN
GO


IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblMultimedia_GetVideoByList]') and type IN (N'PC', N'P'))
DROP PROCEDURE [dbo].[_tblMultimedia_GetVideoByList]
GO

CREATE PROCEDURE [dbo].[_tblMultimedia_GetVideoByList]
(
	@IdList nvarchar(256)
)
AS
	IF(@IdList IS NULL OR @IdList = '')
		SET @IdList = ''''''
	DECLARE @SQL NVARCHAR(2048)
	SET @SQL=N'SELECT * FROM [tblMultimedia] M WHERE M.[Type]=''V'' AND M.[Id] IN ('+@IdList+')'
	print @SQL
	EXEC sp_executesql @SQL
GO
-------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblMultimedia_SetVideoOnHome]') and type IN (N'PC', N'P'))
DROP PROCEDURE [dbo].[_tblMultimedia_SetVideoOnHome]
GO

CREATE PROCEDURE [dbo].[_tblMultimedia_SetVideoOnHome]
(
	@Id int
)
AS
	BEGIN TRAN
		IF (@Id IS NULL OR @Id=0)
			ROLLBACK TRAN
		UPDATE [tblMultimedia] SET [Status]='D' WHERE [Type]='V'
		IF @@ERROR <>0
			ROLLBACK TRAN
		UPDATE [tblMultimedia] SET [Status]='A' WHERE [Id]=@Id AND [Type]='V'
		IF @@ERROR <>0
			ROLLBACK TRAN
	COMMIT TRAN
GO

-------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblAlbum_GetGalleryAlbum]') and type IN (N'PC', N'P'))
DROP PROCEDURE [dbo].[_tblAlbum_GetGalleryAlbum]
GO

CREATE PROCEDURE [dbo].[_tblAlbum_GetGalleryAlbum]
AS
	SELECT *
	FROM tblAlbum
	WHERE [Type] = 'G' AND [Status] = 'A'
GO

-------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblMultimedia_GetPhotoByType]') and type IN (N'PC', N'P'))
DROP PROCEDURE [dbo].[_tblMultimedia_GetPhotoByType]
GO

CREATE PROCEDURE [dbo].[_tblMultimedia_GetPhotoByType]
(
	@Type CHAR(1)
)
AS
	SELECT *
	FROM tblMultimedia
	WHERE (@Type IS NULL OR [Type] = @Type) AND
				[Status] = 'A'
GO